﻿
--SELECT * INTO TBL_CUSTOMER_PRODUCT_HOLDING_201606 FROM [GIANGLH3].[PRODUCT_HOLDING].[DBO].[TBL_VPB_CUSTOMER_20160630] WHERE RECID IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)
DECLARE @INTMONTH INT = 1
DECLARE @SMONTH NVARCHAR(20)
DECLARE @SCOUNTMONTH NVARCHAR(20)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DATE_UPDATE NVARCHAR(100)= (SELECT [DATE_UPDATE] FROM [DATE_EM_REPORT])
DECLARE @MONTH NVARCHAR(100)=(SELECT [MAX_MONTH] FROM [DATE_EM_REPORT])
DECLARE @DATE_END_MONTH NVARCHAR(100)= (SELECT [MAX_DATE_ENDMONTH] FROM [DATE_EM_REPORT])
DECLARE @NOWMONTH INT = MONTH(CONVERT(DATE,@DATE_END_MONTH))
DECLARE @MYDATE DATE = (CONVERT(DATE,@DATE_END_MONTH))
DECLARE @DATE_BEGIN_MONTH NVARCHAR(100)= CONVERT(VARCHAR(25),DATEADD(D,-(DAY(@MYDATE)-1),@MYDATE),112)

--SET @MONTH ='201607'
exec('
IF EXISTS
      (SELECT *
         FROM dbo.sysobjects o
        WHERE o.xtype IN (''U'') AND o.name = ''TBL_CUSTOMER_PRODUCT_HOLDING_'+@month+''')
        DROP TABLE TBL_CUSTOMER_PRODUCT_HOLDING_'+@month+'

SELECT * INTO TBL_CUSTOMER_PRODUCT_HOLDING_'+@month+' FROM [GIANGLH3].[PRODUCT_HOLDING].[DBO].[TBL_VPB_CUSTOMER_'+@date_end_month+'] WHERE RECID IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)
		')

		SELECT * FROM TBL_CUSTOMER_PRODUCT_HOLDING_201601
WHILE @intMonth <=@nowMonth
BEGIN
	set @sMonth= CAST(@intMonth as nvarchar(20))
	SET @SCOUNTMONTH= CAST(@intMonth as nvarchar(20)) 
	if(@INTMONTH < 10) set @SCOUNTMONTH = '0' + @SCOUNTMONTH

	print('1')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_auto is null  THEN ''N''
		  WHEN b.pro_auto =1 THEN ''Y''
			ELSE str(b.pro_auto, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''1'''
	exec(@sql)
	print('2')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_bf is null  THEN ''N''
		  WHEN b.pro_bf =1 THEN ''Y''
			ELSE str(b.pro_bf, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''2'''
	exec(@sql)
	print('3')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_bil is null  THEN ''N''
		  WHEN b.pro_bil =1 THEN ''Y''
			ELSE str(b.pro_bil, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''3'''
	exec(@sql)
	print('4')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_credit_program is null  THEN ''N''
		  WHEN b.pro_credit_program =1 THEN ''Y''
			ELSE str(b.pro_credit_program, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''4'''
	exec(@sql)
	print('5')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_flex is null  THEN ''N''
		  WHEN b.pro_flex =1 THEN ''Y''
			ELSE str(b.pro_flex, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''5'''
	exec(@sql)
	print('6')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_clean_loan is null  THEN ''N''
		  WHEN b.pro_clean_loan =1 THEN ''Y''
			ELSE str(b.pro_clean_loan, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''6'''
	exec(@sql)
	print('7')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_wc is null  THEN ''N''
		  WHEN b.pro_wc =1 THEN ''Y''
			ELSE str(b.pro_wc, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''7'''
	exec(@sql)
	print('8')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_rice is null  THEN ''N''
		  WHEN b.pro_rice =1 THEN ''Y''
			ELSE str(b.pro_rice, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''8'''
	exec(@sql)
	print('9')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_aquatic is null  THEN ''N''
		  WHEN b.pro_aquatic =1 THEN ''Y''
			ELSE str(b.pro_aquatic, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''9'''
	exec(@sql)
	print('10')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_coffee is null  THEN ''N''
		  WHEN b.pro_coffee =1 THEN ''Y''
			ELSE str(b.pro_coffee, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''10'''
	exec(@sql)
	print('11')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_wood is null  THEN ''N''
		  WHEN b.pro_wood =1 THEN ''Y''
			ELSE str(b.pro_wood, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''11'''
	exec(@sql)
	print('12')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_vung4 is null  THEN ''N''
		  WHEN b.pro_vung4 =1 THEN ''Y''
			ELSE str(b.pro_vung4, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''12'''
	exec(@sql)
	print('13')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pro_none is null  THEN ''N''
		  WHEN b.pro_none =1 THEN ''Y''
			ELSE str(b.pro_none, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''13'''
	exec(@sql)
	print('14')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.TGCKH is null  THEN ''N''
		  WHEN b.TGCKH =1 THEN ''Y''
			ELSE str(b.TGCKH, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''14'''
	exec(@sql)
	print('15')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.TGKKH is null  THEN ''N''
		  WHEN b.TGKKH =1 THEN ''Y''
			ELSE str(b.TGKKH, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''15'''
	exec(@sql)
	print('16')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.POS is null  THEN ''N''
		  WHEN b.POS =1 THEN ''Y''
			ELSE str(b.POS, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''16'''
	exec(@sql)
	print('17')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.card_credit is null  THEN ''N''
		  WHEN b.card_credit =1 THEN ''Y''
			ELSE str(b.card_credit, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''17'''
	exec(@sql)
	print('18')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.card_debit is null  THEN ''N''
		  WHEN b.card_debit =1 THEN ''Y''
			ELSE str(b.card_debit, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''18'''
	exec(@sql)
	print('19')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.pay_roll is null  THEN ''N''
		  WHEN b.pay_roll =1 THEN ''Y''
			ELSE str(b.pay_roll, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''19'''
	exec(@sql)
	print('20')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.gd_noibo is null  THEN ''N''
		  WHEN b.gd_noibo =1 THEN ''Y''
			ELSE str(b.gd_noibo, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''20'''
	exec(@sql)
	print('21')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.gd_quocte is null  THEN ''N''
		  WHEN b.gd_quocte =1 THEN ''Y''
			ELSE str(b.gd_quocte, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''21'''
	exec(@sql)
	print('22')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.i2b is null  THEN ''N''
		  WHEN b.i2b =1 THEN ''Y''
			ELSE str(b.i2b, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''22'''
	exec(@sql)
	print('23')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.banca is null  THEN ''N''
		  WHEN b.banca =1 THEN ''Y''
			ELSE str(b.banca, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''23'''
	exec(@sql)
	print('24')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.UPAS is null  THEN ''N''
		  WHEN b.UPAS =1 THEN ''Y''
			ELSE str(b.UPAS, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''24'''
	exec(@sql)
	print('25')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.LC is null  THEN ''N''
		  WHEN b.LC =1 THEN ''Y''
			ELSE str(b.LC, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''25'''
	exec(@sql)
	print('26')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.chiet_khau is null  THEN ''N''
		  WHEN b.chiet_khau =1 THEN ''Y''
			ELSE str(b.chiet_khau, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''26'''
	exec(@sql)
	print('27')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.FX is null  THEN ''N''
		  WHEN b.FX =1 THEN ''Y''
			ELSE str(b.FX, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''27'''
	exec(@sql)
	print('28')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.tai_tro_truoc_giao_hang is null  THEN ''N''
		  WHEN b.tai_tro_truoc_giao_hang =1 THEN ''Y''
			ELSE str(b.tai_tro_truoc_giao_hang, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''28'''
	exec(@sql)
	print('29')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.tt_chuoi is null  THEN ''N''
		  WHEN b.tt_chuoi =1 THEN ''Y''
			ELSE str(b.tt_chuoi, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''29'''
	exec(@sql)
	print('30')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.bao_lanh_thanh_toan is null  THEN ''N''
		  WHEN b.bao_lanh_thanh_toan =1 THEN ''Y''
			ELSE str(b.bao_lanh_thanh_toan, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''30'''
	exec(@sql)
	print('31')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.bao_lanh_th_hop_dong is null  THEN ''N''
		  WHEN b.bao_lanh_th_hop_dong =1 THEN ''Y''
			ELSE str(b.bao_lanh_th_hop_dong, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''31'''
	exec(@sql)
	print('32')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.bao_lanh_du_thau is null  THEN ''N''
		  WHEN b.bao_lanh_du_thau =1 THEN ''Y''
			ELSE str(b.bao_lanh_du_thau, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''32'''
	exec(@sql)
	print('33')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.bao_lanh_hoa_tam_ung is null  THEN ''N''
		  WHEN b.bao_lanh_hoa_tam_ung =1 THEN ''Y''
			ELSE str(b.bao_lanh_hoa_tam_ung, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''33'''
	exec(@sql)
	print('34')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.ba0_lanh_thanh_toan_thue is null  THEN ''N''
		  WHEN b.ba0_lanh_thanh_toan_thue =1 THEN ''Y''
			ELSE str(b.ba0_lanh_thanh_toan_thue, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''34'''
	exec(@sql)
	print('35')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.bao_lanh_bao_hanh is null  THEN ''N''
		  WHEN b.bao_lanh_bao_hanh =1 THEN ''Y''
			ELSE str(b.bao_lanh_bao_hanh, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''35'''
	exec(@sql)
	print('36')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.bao_lanh_doi_ung is null  THEN ''N''
		  WHEN b.bao_lanh_doi_ung =1 THEN ''Y''
			ELSE str(b.bao_lanh_doi_ung, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''36'''
	exec(@sql)
	print('37')
	set @sql=N'update a
	set a.[month'+@sMonth+'] =(CASE WHEN b.nho_thu is null  THEN ''N''
		  WHEN b.nho_thu =1 THEN ''Y''
			ELSE str(b.nho_thu, 25,0)  END)
	from TBL_ACCOUNT_PLANNING_PRODUCT_HOLDING_RESULT as a, [TBL_CUSTOMER_PRODUCT_HOLDING_2017'+@SCOUNTMONTH+'] as b
	where a.cif=b.recid and a.order_num =''37'''
	exec(@sql)
    SET @intMonth = @intMonth + 1
END
GO